package cn.life.business.schedule.dao.impl;

import cn.life.business.material.entity.po.MaterialAuditPo;
import cn.life.business.material.entity.vo.MaterialRecVo;
import cn.life.business.schedule.entity.po.ScheduleAuditPo;
import cn.life.common.entity.vo.Pagination;
import cn.life.common.support.jdbc.AbstractSupportDao;
import cn.life.business.schedule.dao.ScheduleDao;
import cn.life.business.schedule.entity.po.Schedule;
import cn.life.business.schedule.entity.vo.ScheduleVo;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author 王振宇
 * @Description:
 * @Date 2018-07-13 17:15:12
 */
@Repository
public class ScheduleDaoImpl extends AbstractSupportDao implements ScheduleDao{

    @Override
    public Long addSchedule(Schedule schedule) {
        Long recordId = queryTableNextId("factory_schedule","record_id");
        String sql= "insert into factory_schedule(record_id,schedule_name,preparation_stage,base_stage," +
                "subject_stage,decorate_stage,green_stage,project_id,project_name,opr_user_id," +
                "opr_user_name,opr_date,reserved_field1,reserved_field2,reserved_field3) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        return update(sql,recordId,schedule.getScheduleName(),schedule.getPreparationStage(),schedule.getBaseStage(),
                schedule.getSubjectStage(),schedule.getDecorateStage(),schedule.getGreenStage(),schedule.getProjectId(),
                schedule.getProjectName(),schedule.getOprUserId(),schedule.getOprUserName(),schedule.getOprDate(),schedule.getReservedField1(),schedule.getReservedField2(),schedule.getReservedField3()) == 1 ? recordId : null;
    }

    @Override
    public int deleteSchedule(Schedule schedule) {
        int res = 0;
        String sql= "delete from factory_schedule where record_id=?";
        String auditSql = "delete from schedule_audit_record where record_id=?";
        if(update(sql,schedule.getRecordId()) == 1){
            res = update(auditSql,schedule.getRecordId());
        }
        return res;
    }

    @Override
    public int updateSchedule(Schedule schedule) {
        String sql= "update factory_schedule set schedule_name=?,preparation_stage=?," +
                "base_stage=?,subject_stage=?,decorate_stage=?,green_stage=?,reserved_field1=?,reserved_field2=?,reserved_field3=? " +
                "where record_id=?";
        return update(sql,schedule.getScheduleName(),schedule.getPreparationStage(),schedule.getBaseStage(),
                schedule.getSubjectStage(),schedule.getDecorateStage(),schedule.getGreenStage(),
                schedule.getReservedField1(),schedule.getReservedField2(),schedule.getReservedField3(),schedule.getRecordId());
    }

    @Override
    public Pagination<ScheduleVo> selectSchedule(ScheduleVo scheduleVo) {
        StringBuffer stringBuffer=new StringBuffer("select * from factory_schedule where 1=1 ");
        if (!"".equals(scheduleVo.getScheduleName())&&scheduleVo.getScheduleName()!=null){
            stringBuffer.append(" and schedule_name like '%"+scheduleVo.getScheduleName()+"%' ");
        }
        if (!"".equals(scheduleVo.getProjectName())&&scheduleVo.getProjectName()!=null){
            stringBuffer.append(" and project_name like '%"+scheduleVo.getProjectName()+"%' ");
        }
        if (!"".equals(scheduleVo.getSelectDate())&&scheduleVo.getSelectDate()!=null){
            stringBuffer.append(" and  '" + getSqlDate(scheduleVo.getSelectDate()) + "' <=opr_date");
        }
        stringBuffer.append(" ORDER BY opr_date DESC");

        return queryForListByPageEx(stringBuffer.toString(),ScheduleVo.class,scheduleVo.getPageIndex(),scheduleVo.getPageSize());
    }

    @Override
    public ScheduleVo getSchedule(ScheduleVo scheduleVo) {
        String sql="select a.* from (select a.*,(select project_name from project_info where project_id = a.project_id) as projectName,b.opr_user_id as nextUserId,b.opr_user_name as nextUserName,round((a.preparation_stage + a.base_stage + a.subject_stage + a.decorate_stage + a.green_stage)/5,2) as allPercent from factory_schedule a inner join schedule_audit_record b on a.record_id = b.record_id where a.record_id = ? order by b.rec_id desc) a limit 1";
        return queryForObject(sql,ScheduleVo.class,scheduleVo.getRecordId());
    }

    @Override
    public void addScheduleAuditRecord(ScheduleAuditPo scheduleAuditPo) {
        String sql = "insert into schedule_audit_record(record_id,opr_user_id,opr_user_name,opr_date,opr_state,rec_state) values(?,?,?,?,?,?)";
        update(sql,scheduleAuditPo.getRecordId(),scheduleAuditPo.getOprUserId(),scheduleAuditPo.getOprUserName(),getNowDate(),scheduleAuditPo.getOprState(),scheduleAuditPo.getRecState());
    }

    @Override
    public void updateScheduleAuditResult(ScheduleVo scheduleVo) {
        String sql = "update factory_schedule set audit_state = ? where record_id = ?";
        update(sql,scheduleVo.getAuditState(),scheduleVo.getRecordId());
    }

    @Override
    public void discardSchedlueAuditRec(Long recordId) {
        String sql = "update factory_audit_record set rec_state = 0 where record_id = ?";
        update(sql,recordId);
    }

    @Override
    public List<Schedule> queryRecordList(Long userId, String scheduleName,Long projectId) {
        String sql = "select distinct a.*,1 as isMyAudit,round((a.preparation_stage + a.base_stage + a.subject_stage + a.decorate_stage + a.green_stage)/5,2) as allPercent from factory_schedule a inner join schedule_audit_record b on a.record_id = b.record_id and a.audit_state = 1 and b.rec_state = 1 and (a.opr_user_id = ? or b.opr_user_id = ?) and a.project_id = ? and a.schedule_name like ('%" + scheduleName + "%') order by a.opr_date desc";
        return queryForList(sql,Schedule.class,userId,userId,projectId);
    }

    @Override
    public List<Schedule> queryOprRecordList(Long userId, String scheduleName,Long projectId) {
        String sql = "select distinct a.*,1 as isMyAudit,round((a.preparation_stage + a.base_stage + a.subject_stage + a.decorate_stage + a.green_stage)/5,2) as allPercent from factory_schedule a inner join schedule_audit_record b on a.record_id = b.record_id and a.audit_state = 1 and (a.opr_user_id = ? or b.opr_user_id = ?) and a.project_id = ? and a.schedule_name like ('%" + scheduleName + "%') order by a.opr_date desc";
        return queryForList(sql,Schedule.class,userId,userId,projectId);
    }

    @Override
    public List<Schedule> queryLastRecordList(Long userId,String scheduleName, Long projectId,Integer auditState,String projectName) {
        String sql = "select t.* from (select a.*,c.project_name as projectName,b.opr_user_id as nextUserId,b.opr_user_name as nextUserName,case (b.opr_user_id) = " + userId + " when true then 1 else 0 end AS isMyAudit,round((a.preparation_stage + a.base_stage + a.subject_stage + a.decorate_stage + a.green_stage)/5,2) as allPercent from factory_schedule a inner join (select b.record_id,b.opr_user_id,b.opr_user_name from schedule_audit_record b where b.rec_id = (select max(c.rec_id) from schedule_audit_record c where b.record_id = c.record_id)) b on a.record_id = b.record_id inner join project_info c on a.project_id = c.project_id where a.project_id = ? ";
        if(auditState != null && auditState == 9){
            sql += " and a.audit_state = 9";
        }
        if(projectName != null && !projectName.equals("")){
            sql += " and c.project_name like ('%" + projectName + "%')";
        }
        sql += " and a.schedule_name like ('%" + scheduleName + "%') order by a.opr_date desc) t limit 0,1";
        return queryForList(sql,Schedule.class,projectId);
    }

    @Override
    public void updateScheduleReason(long recordId, String reason) {
        String sql = "update factory_schedule set reason = ? where record_id = ?";
        update(sql,reason,recordId);
    }

    @Override
    public List<Schedule> queryLastRecordStatisticsList(Long userId, String scheduleName, Long projectId) {
        String sql = "select t.*,"
                + "ROUND((select ifnull((select count(1) from factory_inspect a where a.project_id = b.project_id and a.inspect_state = 1 and a.status = 1)/count(1),0) from factory_inspect b where b.project_id = t.project_id and b.inspect_state = 1 ";
                if(projectId != null){
                    sql = sql + " and b.project_id = " + projectId;
                }
                sql = sql + "),2) as quality,";
                sql = sql + "(select (select count(1) from factory_inspect a where a.project_id = b.project_id and a.inspect_state = 2 and a.status = 1)/count(1) from factory_inspect b where b.inspect_state = 2 ";
            if(projectId != null){
                sql = sql + " and b.project_id = " + projectId;
            }
            sql = sql + ") as security ";
            sql = sql +  " from (select a.*,b.opr_user_id as nextUserId,b.opr_user_name as nextUserName,case (b.opr_user_id) = " + userId + " when true then 1 else 0 end AS isMyAudit,round((a.preparation_stage + a.base_stage + a.subject_stage + a.decorate_stage + a.green_stage)/5,2) as allPercent from factory_schedule a inner join (select b.record_id,b.opr_user_id,b.opr_user_name from schedule_audit_record b where b.rec_id = (select max(c.rec_id) from schedule_audit_record c where b.record_id = c.record_id)) b on a.record_id = b.record_id where a.schedule_name like ('%" + scheduleName + "%')";
            if(projectId != null){
                sql = sql + " and a.project_id = " + projectId;
            }
            sql = sql + " order by a.opr_date desc) t group by t.project_id";
        return queryForList(sql,Schedule.class);
    }

    @Override
    public Integer queryNowMonthNumber(Long projectId) {
        String sql = "select count(1) from factory_schedule where project_id = ? and audit_state <> 0 and date_format( opr_date, '%y%m' ) = date_format( curdate( ) , '%y%m' )";
        return queryForObject(sql,Integer.class,projectId);
    }

}
